from sqlalchemy import create_engine, MetaData, Table, select, func, or_, and_
from sqlalchemy.orm import Session
from fastapi import HTTPException, Depends
from models import Comment
from datetime import datetime
from schemas import CommentCreate, ListComment,ItemComment

def get_comment(db: Session, id: int):
    return db.query(Comment).filter(Comment.id == id).first()

def get_comments(db: Session, comment:ListComment ):
    username = comment.username
    content = comment.content
    created_at = comment.created_at
    type = comment.type
    pageSize = comment.pageSize
    pageIndex = comment.pageIndex
    skip = (pageIndex - 1) * pageSize
    query = db.query(Comment)
    if content :
        query = query.filter(
            or_(
                Comment.content.ilike(f"%{content}%"),
                # 可以连续多个模糊搜索
                # Comment.description.ilike(search_term)
            )
        )
      
    if(created_at):
        query = query.filter(Comment.created_at >= created_at)
    if username :
        query = query.filter(Comment.username == username)
    if(type) :
        query = query.filter(Comment.type == type)
    
    # 默认按创建时间倒序排序，获取最新评论
    query = query.order_by(Comment.created_at.desc())
    
    # 查询当前页数据
    total = query.count()
    items = query.offset(skip).limit(pageSize).all()
    
    # 转换为字典列表
    # items_list = [dict(zip(items.keys(), item)) for item in items]
    
    # 计算总页数
    total_pages = (total + pageSize - 1) // pageSize
    
    return {
        "data": items,
        "total": total,
        "pageIndex": pageIndex,
        "pageSize": pageSize,
        "totalPages": total_pages
    }
    # return db.query(Comment).offset(pageIndex).limit(pageSize).all()


def create_comment(db: Session, comment: CommentCreate):
    # db_comment = Comment(**comment.dict())  V1 的方式
    comment_data = comment.model_dump(exclude_none=True)
    # 排除时间字段，让数据库使用默认值
    comment_data.pop('created_at', None)
    comment_data.pop('updated_at', None)
    comment_data.pop('deleted_at', None)
    
    db_comment = Comment(**comment_data)
    db.add(db_comment)
    db.commit()
    db.refresh(db_comment)
    return db_comment
"""
删除数据
"""

def del_comments(db: Session, id:int ):
    # 查询要删除的用户
    db_user = db.query(Comment).filter(Comment.id == id,Comment.status!="deleted").first()
    
    if db_user is None:
        raise HTTPException(status_code=404, detail="item not found")
    
    # 删除用户
    # await db.delete(db_user)
    # 软删除
    db_user.status = "deleted"
    db_user.deleted_at = datetime.now()
    db.commit()
    return {"message": "deleted successfully"}

"""
更新数据
"""
def update_comments(db: Session, el: ItemComment ):
    item = db.query(Comment).filter(Comment.id == el.id).first()
    if item is None:
        raise HTTPException(status_code=404, detail="item not found")
        
    # 获取前端实际提供的字段（排除未设置的字段）
    update_data = el.model_dump(exclude_unset=True)
    
    # 仅更新提供的字段
    for field, value in update_data.items():
        setattr(item, field, value)
        # 更新字段
    # # merge方式
    # db.merge(item)
    # db.commit()
    # merge方式 不能使用item
    db.commit()
    db.refresh(item)
    
    return {"message": " updated successfully", "data": item}
